package pe.edutec.app.layer.dao.impl;

import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import pe.edutec.app.domain.Alumno;
import pe.edutec.app.layer.dao.interfaces.AlumnoDao;

/**
 *
 * @author Gustavo Coronel
 */
public class AlumnoDaoImpl implements AlumnoDao {

	@Override
	public Alumno leerPorId(String id) {
		Connection cn = null;
		Alumno alumno = null;
		try {
			cn = AccesoDB.getConnection();
			String sql = "select idalumno, apealumno, nomalumno, diralumno, "
							+ "telalumno, emailalumno, clave "
							+ "from dbo.alumno where idalumno = ?";
			PreparedStatement pstm = cn.prepareStatement(sql);
			pstm.setString(1, id);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				alumno = rowToBean(rs);
			}
			rs.close();
			pstm.close();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			throw new RuntimeException("Error al consultar tabla de alumnos.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
		return alumno;
	}

	@Override
	public List<Alumno> leerPorApellido(String apellido) {
		Connection cn = null;
		List<Alumno> lista = new ArrayList<Alumno>();
		try {
			cn = AccesoDB.getConnection();
			String sql = "select idalumno, apealumno, nomalumno, diralumno, "
							+ "telalumno, emailalumno, clave "
							+ "from dbo.alumno where apealumno like ?";
			PreparedStatement pstm = cn.prepareStatement(sql);
			pstm.setString(1, apellido + "%");
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Alumno a = rowToBean(rs);
				lista.add(a);
			}
			rs.close();
			pstm.close();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			throw new RuntimeException("Error al consultar tabla de alumnos.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
		return lista;
	}

	@Override
	public void crear(Alumno alumno) {
		Connection cn = null;
		String sql, id;
		PreparedStatement pstm;
		Statement stm;
		ResultSet rs;
		int cont;
		try {
			cn = AccesoDB.getConnection();
			cn.setAutoCommit(false);
			cn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			// Leer contador
			sql = "select contador from dbo.Parametro where campo = 'IdAlumno'";
			stm = cn.createStatement();
			rs = stm.executeQuery(sql);
			rs.next();
			cont = rs.getInt("contador");
			rs.close();
			// Crear id
			DecimalFormat df = new DecimalFormat("0000");
			cont++;
			id = "A" + df.format(cont);
			// Insertar registro
			sql = "INSERT INTO dbo.alumno(IdAlumno, ApeAlumno, NomAlumno, "
							+ "DirAlumno, TelAlumno, EmailAlumno, Clave )"
							+ "VALUES(?,?,?,?,?,?,?)";
			pstm = cn.prepareStatement(sql);
			pstm.setString(1, id);
			pstm.setString(2, alumno.getApellido());
			pstm.setString(3, alumno.getNombre());
			pstm.setString(4, alumno.getDireccion());
			pstm.setString(5, alumno.getTelefono());
			pstm.setString(6, alumno.getEmail());
			pstm.setString(7, alumno.getClave());
			pstm.executeUpdate();
			pstm.close();
			// Incrementar contador
			sql = "update dbo.parametro set contador = contador + 1 "
							+ "where campo = 'IdAlumno'";
			stm.executeUpdate(sql);
			// Confirmar Tx
			cn.commit();
			// Retornar codigo
			alumno.setId(id);
		} catch (SQLException e) {
			try {
				cn.rollback();
			} catch (Exception e1) {
			}
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			try {
				cn.rollback();
			} catch (Exception e1) {
			}
			throw new RuntimeException("Error al modificar datos del alumno.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
	}

	@Override
	public void modificar(Alumno alumno) {
		Connection cn = null;
		try {
			cn = AccesoDB.getConnection();
			cn.setAutoCommit(false);
			String sql = "UPDATE dbo.Alumno SET ApeAlumno = ?, NomAlumno = ?, "
							+ "DirAlumno = ?, TelAlumno = ?, EmailAlumno = ? , "
							+ "Clave = ? WHERE IdAlumno = ?";
			PreparedStatement pstm = cn.prepareStatement(sql);
			pstm.setString(1, alumno.getApellido());
			pstm.setString(2, alumno.getNombre());
			pstm.setString(3, alumno.getDireccion());
			pstm.setString(4, alumno.getTelefono());
			pstm.setString(5, alumno.getEmail());
			pstm.setString(6, alumno.getClave());
			pstm.setString(7, alumno.getId());
			int filas = pstm.executeUpdate();
			pstm.close();
			if(filas == 0) {
				throw new SQLException("ID del alumno incorrecto.");
			}
			cn.commit();
		} catch (SQLException e) {
			try {
				cn.rollback();
			} catch (Exception e1) {
			}
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			try {
				cn.rollback();
			} catch (Exception e1) {
			}
			throw new RuntimeException("Error al modificar datos del alumno.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
	}

	@Override
	public void eliminar(String id) {
		Connection cn = null;
		try {
			cn = AccesoDB.getConnection();
			cn.setAutoCommit(false);
			String sql = "DELETE FROM dbo.Alumno WHERE IdAlumno = ?";
			PreparedStatement pstm = cn.prepareStatement(sql);
			pstm.setString(1, id);
			int filas = pstm.executeUpdate();
			pstm.close();
			if(filas == 0) {
				throw new SQLException("ID del alumno incorrecto.");
			}
			cn.commit();
		} catch (SQLException e) {
			try {
				cn.rollback();
			} catch (Exception e1) {
			}
			throw new RuntimeException(e.getMessage());
		} catch (Exception e) {
			try {
				cn.rollback();
			} catch (Exception e1) {
			}
			throw new RuntimeException("Error al eliminar alumno.");
		} finally {
			try {
				cn.close();
			} catch (Exception e) {
			}
		}
	}

	private Alumno rowToBean(ResultSet rs) throws SQLException {
		Alumno a = new Alumno();
		a.setId(rs.getString("idalumno"));
		a.setApellido(rs.getString("apealumno"));
		a.setNombre(rs.getString("nomalumno"));
		a.setDireccion(rs.getString("diralumno"));
		a.setTelefono(rs.getString("telalumno"));
		a.setEmail(rs.getString("emailalumno"));
		a.setClave(rs.getString("clave"));
		return a;
	}
}
